package ph.gov.marikina.mcsmtms.dao.impl;

import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.List;

import org.apache.log4j.Logger;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Transactional;

import ph.gov.marikina.mcsmtms.constants.GlobalConstants;
import ph.gov.marikina.mcsmtms.constants.GlobalConstants.Roads;
import ph.gov.marikina.mcsmtms.constants.GlobalConstants.SituationTypes;
import ph.gov.marikina.mcsmtms.constants.GlobalConstants.Status;
import ph.gov.marikina.mcsmtms.dao.SituationReportDAO;
import ph.gov.marikina.mcsmtms.domain.SituationReport;
import ph.gov.marikina.mcsmtms.domain.User;

@Transactional
public class SituationReportDAOImpl implements SituationReportDAO {
	/** The log. */
	Logger log = Logger.getLogger(SituationReportDAOImpl.class);
	
	private SessionFactory sessionFactory;
	
	@Autowired
	public void setSessionFactory(SessionFactory sessionFactory) {
		this.sessionFactory = sessionFactory;
	}
	
	public Integer addSituationReport(SituationReport situationReport) {
		Session session = sessionFactory.openSession();
		
		User user = new User();
		user.setId(situationReport.getReporter());
		situationReport.setUser(user);
		
		Integer situationReportId = (Integer) session.save(situationReport);
		session.close();
		
		return situationReportId;
	}
	
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public List<SituationReport> getAllSituationReports() {
		Query query = this.sessionFactory.getCurrentSession()
                .createQuery("from SituationReport");
		
		return (List) query.list();
	}
	
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public SituationReport getSituation(String id) {
		Query query = this.sessionFactory.getCurrentSession()
                .createQuery("from SituationReport where id = :id");
		query.setParameter("id", Integer.parseInt(id));
		
		return (SituationReport) query.list().get(0);
	}
	
	public boolean updateSituation(SituationReport situationReport) {
		try {
			Session session = sessionFactory.openSession();
			
			session.getTransaction().begin();
			session.update(situationReport);
			session.getTransaction().commit();

			session.close();
		} catch (Exception ex) {
			ex.printStackTrace();
			return false;
		}
		return true;
	}
	
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public List<SituationReport> getHazardMapSituationReports() {
//		String queryStr = "from SituationReport situationReport, " +
//							"(select max(lastUpdateDateTime) lastUpdateDateTime, road " + 
//								"from SituationReport" +
//								"group by road) latest"; 
//							+
//							"where situationReport.lastUpdateDateTime = latest.lastUpdateDateTime " +
//							"and situationReport.road = latest.road";
		
		String queryStr = "from SituationReport " +
							"where (lastUpdateDateTime, road, direction) in " + 
							"(select max(lastUpdateDateTime), road, direction from SituationReport " +
							"where status = ? " +
							//"and lastUpdateDateTime >= ? and lastUpdateDateTime <= ? " +
							"group by road, direction)";
		
//		Calendar cal = new GregorianCalendar();
//		Date fromDate = new Date();
//		cal.setTime(fromDate);
//		
//		cal.set(cal.HOUR_OF_DAY, 0);
//		cal.set(cal.MINUTE, 0);
//		cal.set(cal.SECOND, 0);
//		fromDate = cal.getTime();
//		
//		cal.set(cal.HOUR_OF_DAY, 23);
//		cal.set(cal.MINUTE, 59);
//		cal.set(cal.SECOND, 59);
//		Date endDate = cal.getTime();
		
		Query query = this.sessionFactory.getCurrentSession()
                .createQuery(queryStr).setParameter(0, Status.VALIDATED.getCode());
//                .setParameter(1, fromDate)
//                .setParameter(2, endDate);
		
		return (List) query.list();
	}
	
	@Override
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public List<SituationReport> getHazardMapSituationReports(String direction) {
		String queryStr = "from SituationReport " +
							"where (lastUpdateDateTime, road, direction) in " + 
							"(select max(lastUpdateDateTime), road, direction from SituationReport " +
							"where status = ? " +
							"and direction = ? " +
							//"and lastUpdateDateTime >= ? and lastUpdateDateTime <= ? " +
							"group by road, direction)";
		
//		Calendar cal = new GregorianCalendar();
//		Date fromDate = new Date();
//		cal.setTime(fromDate);
//		
//		cal.set(cal.HOUR_OF_DAY, 0);
//		cal.set(cal.MINUTE, 0);
//		cal.set(cal.SECOND, 0);
//		fromDate = cal.getTime();
//		
//		cal.set(cal.HOUR_OF_DAY, 23);
//		cal.set(cal.MINUTE, 59);
//		cal.set(cal.SECOND, 59);
//		Date endDate = cal.getTime();
		
		Query query = this.sessionFactory.getCurrentSession()
                .createQuery(queryStr).setParameter(0, Status.VALIDATED.getCode()).setParameter(1, direction);
//                .setParameter(1, fromDate)
//                .setParameter(2, endDate);
		
		return (List) query.list();
	}
	
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public List<SituationReport> getNotifications() {
		String queryStr = "from SituationReport " +
							"where status = ?";
		
		Query query = this.sessionFactory.getCurrentSession()
                .createQuery(queryStr).setParameter(0, Status.NEW.getCode());
		
		return (List) query.list();
	}
	
	@Override
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public List<SituationReport> getForValidationNotifications() {
		String queryStr = "from SituationReport " +
							"where status = ?";
		
		Query query = this.sessionFactory.getCurrentSession()
                .createQuery(queryStr).setParameter(0, Status.FOR_VALIDATION.getCode());
		
		return (List) query.list();
	}
	
	@Override
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public List<SituationReport> getForValidationNotifications(String road) {
		String queryStr = "from SituationReport " +
							"where status = ?" +
							" and road = ?";
		
		Query query = this.sessionFactory.getCurrentSession()
                .createQuery(queryStr)
                .setParameter(0, Status.FOR_VALIDATION.getCode())
                .setParameter(1, road);
		
		return (List) query.list();
	}
	
	@Override
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public List<SituationReport> getReportsForDateRange(Date startDt, Date endDt, String status) {
		String queryStr = "from SituationReport " +
							"where status = ? " + 
							"and lastUpdateDateTime >= ? and lastUpdateDateTime <= ?";
		
		Query query = this.sessionFactory.getCurrentSession()
                .createQuery(queryStr)
                .setParameter(0, status)
                .setTimestamp(1, startDt)
                .setTimestamp(2, endDt);
		
		return (List) query.list();
	}
	
	@Override
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public List getSituationCountsForDateRange(Date startDt, Date endDt, String status) {
		String queryStr = "select r.road, s.situation, count(r.road) " +
							"from SituationReport r left join r.situationSegmentList s " +
							//"with r.id = s.situation_report_id " +
							"where r.status = ? " + 
							"and r.lastUpdateDateTime >= ? and r.lastUpdateDateTime <= ? " +
							"group by r.road, s.situation";
		
		Query query = this.sessionFactory.getCurrentSession()
                .createQuery(queryStr)
                .setParameter(0, status)
                .setTimestamp(1, startDt)
                .setTimestamp(2, endDt);
		
		return (List) query.list();
	}
	
//	select r.road, r.date_time fromTime, (select r2.date_time
//			from situation_report r2 
//			where r2.date_time > r.date_time and r2.status='VALIDATED') toTime
//from situation_report r 
//left outer join situation_segment s
//on r.id = s.situation_report_id
//where r.status = 'VALIDATED'
//and s.situation = 'FLOOD'
	
//	@Override
//	@SuppressWarnings({ "unchecked", "rawtypes" })
//	public List getFrequentlyFloodedStreets(Date startDt, Date endDt, String status) {
//		String queryStr = "select r.road, r.lastUpdateDateTime, (select r2.lastUpdateDateTime from SituationReport as r2 " +
//																	"where r2.lastUpdateDateTime > r.lastUpdateDateTime " +
//																	"and r2.status = 'VALIDATED' " +
//																	"and r2.road = r.road " +
//																	"order by r2.lastUpdateDateTime ASC 1) " +
//							"from SituationReport as r left join r.situationSegmentList as s " +
//							"where r.status = ? " + 
//							"and r.lastUpdateDateTime >= ? and r.lastUpdateDateTime <= ? " +
//							"and s.situation = 'FLOOD'";
//		
//		Query query = this.sessionFactory.getCurrentSession()
//                .createQuery(queryStr)
//                .setParameter(0, status)
//                .setTimestamp(1, startDt)
//                .setTimestamp(2, endDt);
//		
//		return (List) query.list();
//	}
	
	@Override
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public List getFrequentlyFloodedStreets(Date startDt, Date endDt, String status) {
		String queryStr = "select r.road, r.date_time, (select r2.date_time from situation_report as r2 " +
																	"where r2.date_time > r.date_time " +
																	"and r2.status = 'VALIDATED' " +
																	"and r2.road = r.road " +
																	"order by r2.date_time asc LIMIT 1) " +
							"from situation_report as r left join situation_segment as s " +
							"on r.id = s.situation_report_id " +
							"where r.status = ? " + 
							"and r.date_time >= ? and r.date_time <= ? " +
							"and s.situation = ? " +
							"order by r.road asc, r.date_time desc";
		
		SQLQuery query = (SQLQuery) this.sessionFactory.getCurrentSession()
              .createSQLQuery(queryStr)
              .setParameter(0, status)
              .setTimestamp(1, startDt)
              .setTimestamp(2, endDt)
              .setString(3, SituationTypes.FLOOD.getCode());
		
		return (List) query.list();
	}
	
	@Override
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public List getMostAccidentProneStreets(Date startDt, Date endDt, String status) {
		String queryStr = "select r.road, r.date_time, (select r2.date_time from situation_report as r2 " +
																	"where r2.date_time > r.date_time " +
																	"and r2.status = 'VALIDATED' " +
																	"and r2.road = r.road " +
																	"order by r2.date_time asc LIMIT 1) " +
							"from situation_report as r left join situation_segment as s " +
							"on r.id = s.situation_report_id " +
							"where r.status = ? " + 
							"and r.date_time >= ? and r.date_time <= ? " +
							"and s.situation = ? " +
							"order by r.road asc, r.date_time desc";
		
		SQLQuery query = (SQLQuery) this.sessionFactory.getCurrentSession()
              .createSQLQuery(queryStr)
              .setParameter(0, status)
              .setTimestamp(1, startDt)
              .setTimestamp(2, endDt)
              .setString(3, SituationTypes.ACCIDENT.getCode());
		
		return (List) query.list();
	}
	
	@Override
	public List getAccidentCountsPerRoad(Date startDt, Date endDt, String status) {
		String queryStr = "select r.road, count(r.road) " +
							"from situation_report as r left join situation_segment as s " +
							"on r.id = s.situation_report_id " +
							"where r.status = ? " + 
							"and r.date_time >= ? and r.date_time <= ? " +
							"and s.situation = ? " +
							"group by r.road";
			
		SQLQuery query = (SQLQuery) this.sessionFactory.getCurrentSession()
			.createSQLQuery(queryStr)
			.setParameter(0, status)
			.setTimestamp(1, startDt)
			.setTimestamp(2, endDt)
			.setString(3, SituationTypes.ACCIDENT.getCode());
			
		return (List) query.list();
	}
	
	@Override
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public List getMostCongestedStreets(Date startDt, Date endDt, String status) {
		String queryStr = "select r.road, r.date_time, " +
								"(select r2.date_time from situation_report as r2 " +
									"where r2.date_time > r.date_time " +
									"and r2.status = 'VALIDATED' " +
									"and r2.road = r.road " +
									"order by r2.date_time asc LIMIT 1), " +
								" s.situation " +
							"from situation_report as r left join situation_segment as s " +
							"on r.id = s.situation_report_id " +
							"where r.status = ? " +
							"and s.trafficCondition = ? " + 
							"and r.date_time >= ? and r.date_time <= ? " +
							"order by r.road asc, r.date_time desc";
		
		SQLQuery query = (SQLQuery) this.sessionFactory.getCurrentSession()
              .createSQLQuery(queryStr)
              .setParameter(0, status)
              .setParameter(1, GlobalConstants.TrafficConditionTypes.HEAVY.getCode())
              .setTimestamp(2, startDt)
              .setTimestamp(3, endDt);
		
		return (List) query.list();
	}
	
}
